﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CSP.Dao.IObjectDaos.CSPBBS;
using CSP.Model.CSPBBS;
using MySql.Data.MySqlClient;
using CSP.DataAccess;
using System.Data;
using CSP.Common.IProviders;
using CSP.Common.Factories;
using System.Collections.ObjectModel;
using CSP.Dao.CommonDao;
using CSP.Common.Tools;

namespace CSP.Dao.ObjectDaos.CSPBBS
{
    public class CommonMemberDao : CSPBBSCommonDao, ICommonMemberDao
    {
        private const string TABLE = "csp_common_member";

        /// <summary>
        /// 获取所有的对象
        /// 范围：
        /// </summary>
        /// <returns></returns>
        public ICollection<CommonMember> FindAll()
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();
            DataSet ds = null;
            try
            {
                ds = MySqlHelperFactory.ExecuteDataset(conn, CommandType.Text, "select * from " + TABLE);
                //MySqlDataReader reader1 = MySqlHelperFactory.ExecuteReader(conn, CommandType.Text, "select * from " + TABLE);
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("FindAll.GetDataById", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return DataSetToMembers(ds);
        }

        /// <summary>
        /// 根据Id 获取对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CommonMember GetDataById(int id)
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();
            MySqlDataReader reader = null;

            CommonMember result = null;
            try
            {
                reader = MySqlHelperFactory.ExecuteReader(conn, CommandType.Text, "select * from " + TABLE + " where uid =" + id);
                ICollection<CommonMember> members = ReaderToMembers(reader);
                result = members.ElementAtOrDefault(0);
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("CommonMemberDao.GetDataById", ex);
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                conn.Close();
            }
            return result;
        }


        /// <summary>
        /// 根据用户名获取基本信息
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns></returns>
        public CommonMember GetInfoByUserName(string userName)
        {
            if (string.IsNullOrWhiteSpace(userName))
            {
                throw new Exception("userName Can not be null");
            }
            CommonMember result = null;
            string selectSql = "select * from " + TABLE + " where username=@username";

            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();
            MySqlDataReader reader = null;
            try
            {
                reader = MySqlHelperFactory.ExecuteReader(conn, CommandType.Text, selectSql, GetParameter("@username", userName));
                reader.Read();
                result = ObjsToMember<MySqlDataReader>(reader);
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("CommonMemberDao.GetInfoByUserName", ex);
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                conn.Close();
            }
            return result;
        }

        /// <summary>
        /// 根据用户名密码获取数据
        /// </summary>
        /// <param name="uid">用户名</param>
        /// <param name="pwd">密码</param>
        /// <returns></returns>
        public CommonMember GetInfoByUidAndPwd(string uid, string pwd)
        {
            CommonMember result = null;
            string selectSql = "select * from " + TABLE + " where uid=@uid and password=@password";

            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();
            MySqlDataReader reader = null;
            try
            {
                reader = MySqlHelperFactory.ExecuteReader(conn, CommandType.Text, selectSql, GetParameter("@uid", uid), GetParameter("@password", pwd));
                reader.Read();
                result = ObjsToMember<MySqlDataReader>(reader);
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("CommonMemberDao.GetInfoByUidAndPwd", ex);
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                conn.Close();
            }
            return result;
        }

        /// <summary>
        /// 根据Ids获取 年龄段 数据
        /// 范围：
        /// </summary>
        /// <returns></returns>
        public ICollection<CommonMember> GeDatatByIds(int[] Ids)
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();
            MySqlDataReader reader = null;

            ICollection<CommonMember> result;
            try
            {
                reader = MySqlHelperFactory.ExecuteReader(conn, CommandType.Text, "select * from " + TABLE + " where uid in (" + string.Join(",", Ids) + ")");
                result = ReaderToMembers(reader);
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("CommonMemberDao.GeDatatByIds", ex);
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                conn.Close();
            }

            return result;
        }

        /// <summary>
        /// 添加对象
        /// </summary>
        /// <param name="ageGruop"></param>
        public void Add(CommonMember obj)
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();

            string insertSql = "insert into " + TABLE + " (uid,username,password,email,status,emailstatus,avatarstatus,videophotostatus,adminid,groupid,groupexpiry,extgroupids,regdate,credits,notifysound,timeoffset,newpm,newprompt,accessmasks,allowadmincp,onlyacceptfriendpm,conisbind) values";
            insertSql += "(@uid,@username,@password,@email,@status,@emailstatus,@avatarstatus,@videophotostatus,@adminid,@groupid,@groupexpiry,@extgroupids,@regdate,@credits,@notifysound,@timeoffset,@newpm,@newprompt,@accessmasks,@allowadmincp,@onlyacceptfriendpm,@conisbind)";
            MySqlParameter[] parms = GetParmsForAddOrUpdate(obj);
            try
            {
                int result = MySqlHelperFactory.ExecuteNonQuery(conn, CommandType.Text, insertSql, parms);
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("CommonMemberDao.Add", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 通过对象 更新对象
        /// </summary>
        /// <param name="obj"></param>
        public void Update(CommonMember obj)
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();

            string updateSql = "update " + TABLE + "  set username=@username,password=@password,email=@email,status=@status,emailstatus=@emailstatus,avatarstatus=@avatarstatus,videophotostatus=@videophotostatus,adminid=@adminid,groupid=@groupid,groupexpiry=@groupexpiry,extgroupids=@extgroupids,regdate=@regdate,credits=@credits,notifysound=@notifysound,timeoffset=@timeoffset,newpm=@newpm,newprompt=@newprompt,accessmasks=@accessmasks,allowadmincp=@allowadmincp,onlyacceptfriendpm=@onlyacceptfriendpm,conisbind=@conisbind where uid=@uid";

            MySqlParameter[] parms = GetParmsForAddOrUpdate(obj);
            try
            {
                int result = MySqlHelperFactory.ExecuteNonQuery(conn, CommandType.Text, updateSql, parms);
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("CommonMemberDao.Update", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 根据Id删除 数据
        /// 范围：
        /// </summary>
        /// <param name="id"></param>
        public CommonMember DeleteById(int id)
        {
            CommonMember member = this.GetDataById(id);
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();

            string deleteSql = "delete from " + TABLE + " where uid=" + id;

            try
            {
                int result = MySqlHelperFactory.ExecuteNonQuery(conn, CommandType.Text, deleteSql);
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("CommonMemberDao.DeleteById", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return member;
        }

        /// <summary>
        /// 判断用户名是否存在
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public bool IsExsitByUserName(string userName)
        {
            bool result = false;
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();

            string selectSql = "select count(*) as count from " + TABLE + " where username='" + userName + "'";

            try
            {
                int count = Convert.ToInt32(MySqlHelperFactory.ExecuteScalar(conn, CommandType.Text, selectSql));
                if (count > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                ILoggerProvider provider = LoggerFactory.GetErrorLogger();
                provider.Error("CommonMemberDao.IsExsitByUserName", ex);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        private ICollection<CommonMember> DataSetToMembers(DataSet ds)
        {
            ICollection<CommonMember> result = new Collection<CommonMember>();
            if (ds.Tables.Count == 0)
            {
                return result;
            }
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                CommonMember member = ObjsToMember<DataRow>(ds.Tables[0].Rows[i]);
                result.Add(member);
            }
            return result;
        }

        private ICollection<CommonMember> ReaderToMembers(MySqlDataReader reader)
        {
            ICollection<CommonMember> result = new Collection<CommonMember>();
            int i = 0;
            do
            {

                while (reader.Read())
                {
                    CommonMember member = ObjsToMember<MySqlDataReader>(reader);
                    result.Add(member);
                }
                i++;
            } while (reader.NextResult()); //NextResult()移动到下一个记录集 

            return result;
        }

        /// <summary>
        /// 将数组数据做对象对配转换
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objs"></param>
        /// <returns></returns>
        private CommonMember ObjsToMember<T>(T objs)
        {
            if (objs == null)
            {
                return null;
            }
            //获取当前实例类型
            Type type = objs.GetType();
            //获取实例索引器
            var property = type.GetProperty("Item", new Type[] { typeof(string) });

            //创建返回值实例
            CommonMember result = new CommonMember();

            result.Uid = Convert.ToInt32(property.GetValue(objs, new object[] { "uid" }));
            result.UserName = property.GetValue(objs, new object[] { "UserName" }).ToString();
            result.PassWord = property.GetValue(objs, new object[] { "PassWord" }).ToString();
            result.Email = property.GetValue(objs, new object[] { "Email" }).ToString();
            //result.PassWord = property.GetValue(objs, new object[] { "LastLoginIp" }).ToString();
            result.Status = Convert.ToInt32(property.GetValue(objs, new object[] { "Status" }));
            result.EmailStatus = Convert.ToInt32(property.GetValue(objs, new object[] { "EmailStatus" }));
            result.AvatarStatus = Convert.ToInt32(property.GetValue(objs, new object[] { "AvatarStatus" }));

            result.VideoPhotoStatus = Convert.ToInt32(property.GetValue(objs, new object[] { "VideoPhotoStatus" }));
            result.AdminId = Convert.ToInt32(property.GetValue(objs, new object[] { "AdminId" }));
            result.Groupid = Convert.ToInt32(property.GetValue(objs, new object[] { "Groupid" }));
            result.GroupeXpiry = Convert.ToInt32(property.GetValue(objs, new object[] { "GroupeXpiry" }));
            result.ExtGroupIds = property.GetValue(objs, new object[] { "ExtGroupIds" }).ToString();
            result.Regdate = CommonTool.ConvertToDateTime(Convert.ToDouble(property.GetValue(objs, new object[] { "Regdate" })));
            result.Credits = Convert.ToInt32(property.GetValue(objs, new object[] { "Credits" }));

            result.Notifysound = Convert.ToInt32(property.GetValue(objs, new object[] { "Notifysound" }));
            result.TimeOffset = property.GetValue(objs, new object[] { "TimeOffset" }).ToString();
            result.Newpm = Convert.ToInt32(property.GetValue(objs, new object[] { "Newpm" }));
            result.NewPrompt = Convert.ToInt32(property.GetValue(objs, new object[] { "NewPrompt" }));
            result.Accessmasks = Convert.ToInt32(property.GetValue(objs, new object[] { "Accessmasks" }));
            result.Allowadmincp = Convert.ToInt32(property.GetValue(objs, new object[] { "Allowadmincp" }));
            result.OnlyAcceptFriendpm = Convert.ToInt32(property.GetValue(objs, new object[] { "OnlyAcceptFriendpm" }));
            result.Conisbind = Convert.ToInt32(property.GetValue(objs, new object[] { "Conisbind" }));

            return result;
        }

        private MySqlParameter[] GetParmsForAddOrUpdate(CommonMember member)
        {
            MySqlParameter[] parms = new MySqlParameter[] {
                GetParameter("@uid",DbType.Int32,8,member.Uid),
                GetParameter("@email",DbType.String,40,member.Email),
                GetParameter("@username",DbType.String,15,member.UserName),
                GetParameter("@password",DbType.String,32,member.PassWord),
                GetParameter("@status",DbType.Int32,1,member.Status),
                GetParameter("@emailstatus",DbType.Int32,1,member.EmailStatus),
                GetParameter("@avatarstatus",DbType.Int32,1,member.AvatarStatus),
                GetParameter("@videophotostatus",DbType.Int32,1,member.VideoPhotoStatus),
                GetParameter("@adminid",DbType.Int32,1,member.AdminId),
                GetParameter("@groupid",DbType.Int32,6,member.Groupid),
                GetParameter("@groupexpiry",DbType.Int32,10,member.GroupeXpiry),
                GetParameter("@extgroupids",DbType.String,20,member.ExtGroupIds),
                GetParameter("@regdate",DbType.Int32,10,CommonTool.ConvertToTimeSpan(member.Regdate)),
                GetParameter("@credits",DbType.Int32,10,member.Credits),

                GetParameter("@Notifysound",DbType.Int32,1,member.Notifysound),
                GetParameter("@TimeOffset",DbType.String,4,member.TimeOffset),
                GetParameter("@Newpm",DbType.Int32,6,member.Newpm),
                GetParameter("@NewPrompt",DbType.Int32,6,member.NewPrompt),
                GetParameter("@Accessmasks",DbType.Int32,1,member.Accessmasks),
                GetParameter("@Allowadmincp",DbType.Int32,1,member.Allowadmincp),
                GetParameter("@OnlyAcceptFriendpm",DbType.Int32,1,member.OnlyAcceptFriendpm),
                GetParameter("@Conisbind",DbType.Int32,1,member.Conisbind)
            };

            return parms;
        }


    }
}
